﻿namespace BEGDC.CC.ExcelTool
{
    #region Using Directives

    using System;
    using System.IO;
    using System.Runtime.InteropServices;
    using System.Text;
    using System.Web;
    using Microsoft.Office.Interop.Excel;
    using Page = System.Web.UI.Page;

    #endregion

    public class ExcelExporter
    {
        public ApplicationClass MyApp { get; private set; }

        public Worksheet MyWorkSheet { get; private set; }

        public Workbook MyWorkBook { get; private set; }

        public void Open(string templateName)
        {
            this.MyApp = new ApplicationClass
            {
                Visible = false,
                DisplayAlerts = false
            };

            object objMissing = Type.Missing;
            this.MyWorkBook = this.MyApp.Application.Workbooks.Open(templateName, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing, objMissing);

            this.MyWorkSheet = (Worksheet)this.MyWorkBook.Worksheets[1];
        }

        private void CloseExcel()
        {
            object objMissing = Type.Missing;
            try
            {
                if (this.MyWorkBook != null)
                {
                    try
                    {
                        this.MyApp.ActiveWindow.Close(false, objMissing, objMissing);
                    }
                    catch (Exception)
                    {
                    }
                }

                if (this.MyApp != null)
                {
                    try
                    {
                        this.MyApp.Workbooks.Close();
                    }
                    catch (Exception)
                    {
                    }

                    try
                    {
                        this.MyApp.Quit();
                    }
                    catch (Exception)
                    {
                    }
                }
            }
            finally
            {
                if (this.MyWorkSheet != null)
                {
                    try
                    {
                        Marshal.ReleaseComObject(this.MyWorkSheet);
                    }
                    catch (Exception)
                    {
                    }
                    this.MyWorkSheet = null;
                }

                if (this.MyWorkBook != null)
                {
                    try
                    {
                        Marshal.ReleaseComObject(this.MyWorkBook);
                    }
                    catch (Exception)
                    {
                    }
                    this.MyWorkBook = null;
                }

                if (this.MyApp != null)
                {
                    try
                    {
                        Marshal.ReleaseComObject(this.MyApp);
                    }
                    catch (Exception)
                    {
                    }
                    this.MyApp = null;
                }
            }
        }

        public void Close()
        {
            this.CloseExcel();
            GC.Collect();
        }

        public void Save()
        {
            this.MyWorkBook.Save();
        }

        public void ExportAs(string filename)
        {
            this.MyWorkBook.SaveAs(filename, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, false, false, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        }

        public bool WorksheetExist(string worksheetName)
        {
            bool found = false;
            for (int i = 1; i <= this.MyWorkBook.Worksheets.Count; i++)
            {
                var worksheet = (Excel.Worksheet)this.MyWorkBook.Worksheets[i];
                if (string.Compare(worksheet.Name, worksheetName, true) == 0)
                {
                    found = true;
                    break;
                }
            }
            return found;
        }

        /// <summary>
        ///   Write the generated excel file to client
        /// </summary>
        /// <param name = "page">the page call this method</param>
        /// <param name = "sourcefile">the file on server</param>
        /// <param name = "clientFileName"></param>
        public void Export2Client(Page page, string sourcefile, string clientFileName)
        {
            using (var fileStream = new FileStream(sourcefile, FileMode.Open))
            {
                try
                {
                    long fileSize = fileStream.Length;
                    page.Response.Buffer = true;
                    page.Response.ContentType = "application/octet-stream";
                    page.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + HttpUtility.UrlEncode(clientFileName, Encoding.UTF8) + "\"");
                    page.Response.AddHeader("Content-Length", fileSize.ToString());
                    var fileBuffer = new byte[fileSize];
                    fileStream.Read(fileBuffer, 0, (int)fileSize);

                    page.Response.BinaryWrite(fileBuffer);
                    page.Response.Flush();
                    page.Response.End();
                }
                finally
                {
                    File.Delete(sourcefile);
                }
            }
        }
    }
}